--Thailand Database creation script 
--By Zach Wilcock and Logan Green
--For Class CS4750 - Spring 2013 - Professor Richard Fry





------------------------------------------------------
--Use Master database to Drop/Create the new database
------------------------------------------------------
USE Master
GO --Wait until the above statement is done processing before continuing.



IF EXISTS (SELECT * FROM sysdatabases WHERE name='thailanddb') 
	DROP DATABASE thailanddb --if exists database drop it
GO



CREATE DATABASE thailanddb
	ON PRIMARY
	(	NAME = 'thailanddb',
		FILENAME = 'c:\databases\thailanddb.mdf',
		SIZE = 25MB,
		FILEGROWTH = 10% 
	)
	LOG ON
	(	NAME = 'thailanddb_Log',
		FILENAME = 'c:\databases\thailanddb_Log.ldf',
		SIZE = 2MB, 
		FILEGROWTH = 10% 
	) 
GO








---------------------------------------------------------
--Use the new database to Create the new tables
---------------------------------------------------------
USE thailanddb
GO

CREATE TABLE [dbo].[CLIENT](
	[clientID] [int] IDENTITY(1,1) NOT NULL,
	[clientFirstName] [varchar](50) NOT NULL,
	[clientEmail] [varchar](50) NOT NULL,
	[clientLastName] [varchar](50) NULL,
	[clientStateProvidence] [varchar](50) NULL,
	[clientNotes] [varchar](2048) NULL,
	[clientPassportNum] [char](10) NULL,
	[clientAddress] [varchar](50) NULL,
	[clientCity] [varchar](50) NULL,
	[ccISO2] [char](2) NULL,
	[clientDateCreated] [datetime] NULL,
	[clientIsActive] bit NOT NULL
)
GO

CREATE TABLE [dbo].[CLIENTPHONE](
	[clientID] [int] NOT NULL,
	[phoneID] [int] NOT NULL,
	[clientPhoneNumber] [char](20) NOT NULL
)
GO

CREATE TABLE [dbo].[COOKIE](
	[cookieID] [int] NOT NULL,
	[userID] [int] NULL,
	[tokenInt] [int] NULL,
	[dateCreated] [datetime] NULL
)
GO

CREATE TABLE [dbo].[COUNTRYCODE](
	[ccISO2] [char](2) NOT NULL,
	[ccISO3] [char](3) NULL,
	[ccNumCode] [varchar](6) NULL,
	[ccCallingCode] [varchar](8) NULL,
	[ccShortName] [varchar](80) NULL,
	[ccLongName] [varchar](80) NULL
)
GO

CREATE TABLE [dbo].[EMAILPREFERENCE](
	[userID] [int] NOT NULL,
	[onPropInquiry] [bit] NOT NULL,
	[onDeposityMade] [bit] NOT NULL,
	[onRefundMade] [bit] NOT NULL,
	[onReservationDrop] [bit] NOT NULL,
	[onReservationEdit] [bit] NOT NULL,
	[gmailUserName] [varchar](50) NOT NULL,
	[gmailPassword] [varchar](50) NOT NULL,
	[onPropInquiryTemplate] [varchar](max) NULL,
	[onDepositMadeTemplate] [varchar](max) NULL,
	[onRefundMadeTemplate] [varchar](max) NULL,
	[onReservationDropTemplate] [varchar](max) NULL,
	[onReservationEditTemplate] [varchar](max) NULL
)
GO

CREATE TABLE [dbo].[EMAILSENT](
	[emailID] [int] IDENTITY(1,1) NOT NULL,
	[emailDateSent] [datetime] NOT NULL,
	[emailContent] [varchar](max) NOT NULL,
	[clientID] [int] NOT NULL,
	[emailSubject] [varchar](100) NULL
)
GO

CREATE TABLE [dbo].[ERROR](
	[errorID] [int] IDENTITY(1,1) NOT NULL,
	[errorTimeStamp] [datetime] NOT NULL,
	[errorFileName] [varchar](max) NOT NULL,
	[errorFunctionName] [varchar](max) NOT NULL,
	[errorLineNumber] [int] NULL,
	[errorText] [varchar](max) NOT NULL,
	[errorCode] [char](128) NULL,
	[userID] [int] NULL,
	[errorAddInfo] [varchar](max) NULL
)
GO

CREATE TABLE [dbo].[PAYMENT](
	[paymentID] [int] IDENTITY(1,1) NOT NULL,
	[paymentAmount] [money] NOT NULL,
	[resID] [int] NOT NULL,
	[paymentIsDeposit] [bit] NOT NULL,
	[paymentType] [varchar](50) NULL,
	[paymentNotes] [varchar](250) NULL
)
GO

CREATE TABLE [dbo].[PERMISSION](
	[permID] [int] IDENTITY(1,1) NOT NULL,
	[permDetails] [varchar](250) NULL,
	[permLevel] [int] NOT NULL,
	[permTitle] [varchar](250) NULL
)
GO

CREATE TABLE [dbo].[PHONE](
	[phoneID] [int] IDENTITY(1,1) NOT NULL,
	[phoneType] [varchar](30) NOT NULL
)
GO

CREATE TABLE [dbo].[PROPERTY](
	[propID] [int] IDENTITY(1,1) NOT NULL,
	[propName] [varchar](20) NOT NULL,
	[propIsActive] [bit] NOT NULL,
	[propDetails] [varchar](max) NULL,
	[propHyperlink] [varchar](100) NULL,
	[propLocation] [varchar](max) NULL
)
GO

CREATE TABLE [dbo].[RESERVATION](
	[resID] [int] IDENTITY(1,1) NOT NULL,
	[resDate] [datetime] NOT NULL,
	[resStartDate] [datetime] NOT NULL,
	[resEndDate] [datetime] NOT NULL,
	[resQuotedPrice] [money] NOT NULL,
	[resIsHeld] [bit] NOT NULL,
	[propID] [int] NOT NULL,
	[clientID] [int] NOT NULL,
	[userID] [int] NOT NULL
)
GO

CREATE TABLE [dbo].[TASK](
	[taskID] [int] IDENTITY(1,1) NOT NULL,
	[resID] [int] NOT NULL,
	[taskName] [varchar](100) NOT NULL,
	[taskDueDate] [datetime] NOT NULL,
	[taskComplete] [bit] NOT NULL,
	[taskTypeID] [int] NOT NULL,
	[taskDetails] [nvarchar](max) NULL
)
GO

CREATE TABLE [dbo].[TASKTYPE](
	[taskTypeID] [int] IDENTITY(1,1) NOT NULL,
	[taskTypeName] [varchar](50) NOT NULL,
	[isActive] [bit] NULL
)
GO

CREATE TABLE [dbo].[USER](
	[userID] [int] IDENTITY(1,1) NOT NULL,
	[userEmail] [varchar](50) NOT NULL,
	[userFirstName] [varchar](50) NOT NULL,
	[userLastName] [varchar](50) NOT NULL,
	[username] [varchar](50) NOT NULL,
	[userPassword] [varchar](2048) NOT NULL,
	[isActive] [bit] NULL,
	[permID] [int] NOT NULL
)
GO








--------------------------------
--CONSTRAINTS
--------------------------------
ALTER TABLE PERMISSION WITH CHECK ADD
		CONSTRAINT PK_PERMISSION PRIMARY KEY CLUSTERED (permID)
GO

ALTER TABLE [USER] WITH CHECK ADD
		CONSTRAINT PK_USER PRIMARY KEY CLUSTERED (userID),
		CONSTRAINT [D_USER_isActive]  DEFAULT ((1)) FOR [isActive],
		CONSTRAINT [D_USER_permID]  DEFAULT ((1)) FOR [permID],
		CONSTRAINT FK_USER_permID FOREIGN KEY (permID) REFERENCES [PERMISSION](permID)
			ON UPDATE CASCADE
			ON DELETE CASCADE
GO

ALTER TABLE PHONE WITH CHECK ADD
		CONSTRAINT PK_PHONE PRIMARY KEY CLUSTERED (phoneID)
GO

ALTER TABLE COUNTRYCODE WITH CHECK ADD
   	 CONSTRAINT    PK_COUNTRYCODE PRIMARY KEY CLUSTERED (ccISO2)
GO

ALTER TABLE PROPERTY WITH CHECK ADD
   	 CONSTRAINT    PK_PROPERTY PRIMARY KEY CLUSTERED (propID),
	 CONSTRAINT    D_propIsActive DEFAULT 1 FOR propIsActive
GO

ALTER TABLE CLIENT WITH CHECK ADD
		CONSTRAINT	PK_CLIENT PRIMARY KEY CLUSTERED (clientID),
		CONSTRAINT  FK_CLIENT_ccISO2 FOREIGN KEY (ccISO2) REFERENCES COUNTRYCODE(ccISO2)
   		 ON UPDATE CASCADE
   		 ON DELETE CASCADE,
		CONSTRAINT [D_CLIENT_clientDateCreated]  DEFAULT (getdate()) FOR [clientDateCreated],
		CONSTRAINT [D_CLIENT_clientIsActive]  DEFAULT ((1)) FOR [clientIsActive]
GO

ALTER TABLE RESERVATION WITH CHECK ADD
   	 CONSTRAINT    PK_RESERVATION PRIMARY KEY CLUSTERED (resID),
   	 CONSTRAINT    FK_RESERVATION_propID FOREIGN KEY (propID) REFERENCES PROPERTY(propID)
   		 ON UPDATE CASCADE
   		 ON DELETE CASCADE,
   	 CONSTRAINT    FK_RESERVATION_clientID FOREIGN KEY (clientID) REFERENCES CLIENT(clientID)
   		 ON UPDATE CASCADE
   		 ON DELETE CASCADE,
	 CONSTRAINT    FK_RESERVATION_userID FOREIGN KEY (userID) REFERENCES [USER](userID)
   		 ON UPDATE CASCADE
   		 ON DELETE CASCADE,
	 CONSTRAINT	D_resIsHeld	DEFAULT 1 FOR resIsHeld
GO

ALTER TABLE TASKTYPE WITH CHECK ADD
		CONSTRAINT PK_TASKTYPE PRIMARY KEY CLUSTERED (taskTypeID),
		CONSTRAINT [D_TASKTYPE_isActive]  DEFAULT ((1)) FOR [isActive]
GO

ALTER TABLE TASK WITH CHECK ADD
		CONSTRAINT PK_TASK PRIMARY KEY CLUSTERED (taskID),
		CONSTRAINT FK_TASK_resID FOREIGN KEY (resID) REFERENCES RESERVATION(resID)
   		 ON UPDATE CASCADE
   		 ON DELETE CASCADE,
		CONSTRAINT FK_TASK_taskTypeID FOREIGN KEY (taskTypeID) REFERENCES TASKTYPE(taskTypeID)
   		 ON UPDATE CASCADE
   		 ON DELETE CASCADE
GO

ALTER TABLE CLIENTPHONE WITH CHECK ADD
		CONSTRAINT	PK_CLIENTPHONE PRIMARY KEY CLUSTERED (clientID, phoneID),
		CONSTRAINT  FK_CLIENTPHONE_clientID FOREIGN KEY (clientID) REFERENCES CLIENT(clientID)
   		 ON UPDATE CASCADE
   		 ON DELETE CASCADE,
		CONSTRAINT  FK_CLIENTPHONE_phoneID FOREIGN KEY (phoneID) REFERENCES PHONE(phoneID)
   		 ON UPDATE CASCADE
   		 ON DELETE CASCADE
GO

ALTER TABLE PAYMENT WITH CHECK ADD
   	 CONSTRAINT    PK_PAYMENT PRIMARY KEY CLUSTERED (paymentID),
   	 CONSTRAINT    FK_PAYMENT_resID    FOREIGN KEY (resID) REFERENCES RESERVATION(resID)
   		 ON UPDATE CASCADE
   		 ON DELETE CASCADE
GO


ALTER TABLE EMAILSENT WITH CHECK ADD
   	 CONSTRAINT  PK_EMAILSENT PRIMARY KEY CLUSTERED (emailID),
	 CONSTRAINT  FK_EMAILSENT_clientID    FOREIGN KEY (clientID) REFERENCES CLIENT(clientID)
   		 ON UPDATE CASCADE
   		 ON DELETE CASCADE
GO

ALTER TABLE EMAILPREFERENCE WITH CHECK ADD
		CONSTRAINT PK_EMAILPREFERECE PRIMARY KEY CLUSTERED (userID),
		CONSTRAINT FK_EMAILPREFERENCE_userID FOREIGN KEY (userID) REFERENCES [USER](userID)
			ON UPDATE CASCADE
			ON DELETE CASCADE
GO

ALTER TABLE COOKIE WITH CHECK ADD
   	 CONSTRAINT  PK_COOKIE PRIMARY KEY CLUSTERED (cookieID),
	 CONSTRAINT    FK_USER_USERID    FOREIGN KEY (userID) REFERENCES [USER](userID)
   		 ON UPDATE CASCADE
   		 ON DELETE CASCADE
GO

ALTER TABLE ERROR WITH CHECK ADD
		CONSTRAINT	PK_ERROR PRIMARY KEY CLUSTERED (errorID),
		CONSTRAINT D_ERROR_errorTimeStamp DEFAULT (getdate()) FOR errorTimeStamp
GO









-----------------------------------------------------------
--Create Stored procs and Triggers
-----------------------------------------------------------

--Insert New User
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'sp_InsUser'
        )
DROP PROCEDURE sp_InsUser
GO

CREATE PROCEDURE sp_InsUser
            
	@userEmail			VARCHAR(50),
	@userFirstName		VARCHAR(50),
	@userLastName		VARCHAR(50) = NULL,
	@username			VARCHAR(50),
	@userPassword		VARCHAR(2048),
	@permLevel			INT,
	@userPermDetails	VARCHAR(250) = NULL,
	@userIsActive		BIT = 1
	AS
	
	DECLARE	@permID int
	SELECT  @permID = 1
	
	INSERT INTO dbo.[USER] (
		userEmail, 
		userFirstName,
		userLastName, 
		username,
		userPassword,
		isActive,
		permID
		)
	VALUES
		(
		@userEmail, 
		@userFirstName,
		@userLastName, 
		@username,
		@userPassword,
		@userIsActive,
		@permID
		)
		

GO

--Insert New Permission Level
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'sp_InsPerm'
        )
DROP PROCEDURE sp_InsPerm
GO

CREATE PROCEDURE sp_InsPerm
            
	@permLevel			INT,
	@permDetails		VARCHAR(250) = NULL

	AS
	
	
	INSERT INTO dbo.[PERMISSION] (
		permLevel,
		permDetails
		)
	VALUES
		(
		@permLevel, 
		@permDetails
		)
	
GO

--Insert New Country Code
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'sp_InsCountryCode'
        )
DROP PROCEDURE sp_InsCountryCode
GO

CREATE PROCEDURE sp_InsCountryCode
            
	@ccISo2			CHAR(2),
	@ccISO3			CHAR(3)		=	NULL,
	@ccNumCode		VARCHAR(6)	=	NULL,
	@ccCallingCode	VARCHAR(10)	=	NULL,
	@ccShortName	VARCHAR(80)	=	NULL,
	@ccLongName		VARCHAR(80)	=	NULL
	AS
	
	
	INSERT INTO dbo.[COUNTRYCODE] (
		ccISO2, 
		ccISO3,
		ccNumCode, 
		ccCallingCode,
		ccShortName,
		ccLongName
		)
	VALUES
		(
		@ccISO2, 
		@ccISO3,
		@ccNumCode, 
		@ccCallingCode,
		@ccShortName,
		@ccLongName
		)
	
GO

--Insert New Phone Type
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'sp_InsPhoneType'
        )
DROP PROCEDURE sp_InsPhoneType
GO

CREATE PROCEDURE sp_InsPhoneType
            
	@phoneType		VARCHAR(30)
	AS
	
	
	INSERT INTO dbo.[PHONE] (
		phoneType
		)
	VALUES
		(
		@phoneType
		)
	
GO

--Insert New Client Phone 
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'sp_InsClientPhoneNumber'
        )
DROP PROCEDURE sp_InsClientPhoneNumber
GO

CREATE PROCEDURE sp_InsClientPhoneNumber
            
	@clientID			INT,
    @clientPhoneNumber	CHAR(20),
    @clientPhoneType	VARCHAR(30)
	AS
	
	IF NOT EXISTS (SELECT phoneType FROM dbo.[PHONE] WHERE phoneType = @clientPhoneType)
	BEGIN
		EXEC sp_InsPhoneType @clientPhoneType
	END
	
	DECLARE	@phoneID int
	SELECT  @phoneID = phoneID FROM dbo.PHONE WHERE phoneType = @clientPhoneType 
	
	INSERT INTO dbo.[CLIENTPHONE] (
		clientID,
		phoneID,
		clientPhoneNumber
		)
	VALUES
		(
		@clientID,
		@phoneID,
		@clientPhoneNumber
		)
GO

--Insert New Client
--If inserting a new phone number include the number and type
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'sp_InsClient'
        )
DROP PROCEDURE sp_InsClient
GO

CREATE PROCEDURE sp_InsClient
            
    @clientFirstName VARCHAR(50),
    @clientLastName VARCHAR(50) = NULL,
    @clientEmail VARCHAR(50) = 'Unknown', 
    @clientPhoneNumber CHAR(20) = NULL,
    @clientPhoneType VARCHAR(30) = NULL,
    @clientStateProvidence VARCHAR(50) = NULL,
    @clientNotes VARCHAR(MAX) = NULL,
    @clientPassportNum CHAR(10) = NULL,
    @clientAddress VARCHAR(50) = NULL,
    @clientCity VARCHAR(50) = NULL,
    @ccISO2 CHAR(2) = NULL,
	@clientDateCreated DATETIME = NULL


	AS
	
	DECLARE	@clientIsActive int
	SELECT  @clientIsActive = 1
	
	
	INSERT INTO dbo.[CLIENT] (
		clientFirstName,
		clientLastName,
		clientEmail, 
		clientStateProvidence,
		clientNotes,
		clientPassportNum,
		clientAddress,
		clientCity,
		ccISO2,
		clientDateCreated,
		clientIsActive
		)
	VALUES
		(
		@clientFirstName,
		@clientLastName,
		@clientEmail, 
		@clientStateProvidence,
		@clientNotes,
		@clientPassportNum,
		@clientAddress,
		@clientCity,
		@ccISO2,
		@clientDateCreated,
		@clientIsActive
		)
		
	DECLARE @clientID int
	SELECT @clientID = @@identity
	
	IF (@clientPhoneNumber IS NOT NULL AND @clientPhoneType IS NOT NULL)
	BEGIN
	
		EXEC sp_InsClientPhoneNumber @clientID, @clientPhoneNumber, @clientPhoneType
	END

GO

--Insert New Email Sent
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'sp_InsEmailSent'
        )
DROP PROCEDURE sp_InsEmailSent
GO

CREATE PROCEDURE sp_InsEmailSent
            
	@emailDateSent	DATETIME,
    @emailContent	VARCHAR(MAX),
    @clientID		INT,
	@emailSubject    VARCHAR(100) = NULL
	AS
	
	INSERT INTO dbo.[EMAILSENT] (
		emailDateSent,
		emailContent,
		clientID,
		emailSubject
		)
	VALUES
		(
		@emailDateSent,
		@emailContent,
		@clientID,
		@emailSubject
		)
GO

--Insert New Property
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'sp_InsProperty'
        )
DROP PROCEDURE sp_InsProperty
GO

CREATE PROCEDURE sp_InsProperty
            
	@propName		VARCHAR(20),
    @propDetails	VARCHAR(MAX) = NULL,
    @propHyperlink	VARCHAR(100) = NULL,
    @propLocation	VARCHAR(MAX) = NULL
	AS
	
	DECLARE @propIsActive BIT  --Sets new properties to active unless disabled by the user
	SELECT @propIsActive = 1
	
	INSERT INTO dbo.[PROPERTY] (
		propName,
		propDetails,
		propIsActive,
		propHyperlink,
		propLocation
		)
	VALUES
		(
		@propName,
		@propDetails,
		@propIsActive,
		@propHyperlink,
		@propLocation
		)
GO

--Insert New Reservation
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'sp_InsReservation'
        )
DROP PROCEDURE sp_InsReservation
GO

CREATE PROCEDURE sp_InsReservation
    @resStartDate			DATETIME,
    @resEndDate				DATETIME,
    @resQuotedPrice			MONEY,
    @propID					INT,
    @clientID				INT,
    @userID					INT

	AS
	
	DECLARE	@resDate DATETIME
	SELECT  @resDate = getdate()
	
	DECLARE @resIsHeld BIT
	SELECT @resIsHeld = 1
	
	IF (@resStartDate < @resEndDate)
	BEGIN
		INSERT INTO dbo.[RESERVATION] (
			resDate,
			resStartDate,
			resEndDate,
			resQuotedPrice,
			resIsHeld,
			propID,
			clientID,
			userID
			)
		VALUES
			(
			@resDate,
			@resStartDate,
			@resEndDate,
			@resQuotedPrice,
			@resIsHeld,
			@propID,
			@clientID,
			@userID
			)
	END
GO

--Insert New Payment
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'sp_InsPayment'
        )
DROP PROCEDURE sp_InsPayment
GO

CREATE PROCEDURE sp_InsPayment
            
	@paymentAmount		MONEY,
	@resID				INT,
	@paymentIsDeposit	BIT,
    @paymentType		VARCHAR(50) = NULL,
    @paymentNotes		VARCHAR(250) = NULL
    
	AS
	
	IF EXISTS (SELECT resID FROM dbo.RESERVATION WHERE resID = @resID)
	BEGIN
		INSERT INTO dbo.[PAYMENT] (
			paymentAmount,
			resID,
			paymentIsDeposit,
			paymentType,
			paymentNotes
			)
		VALUES
			(
			@paymentAmount,
			@resID,
			@paymentIsDeposit,
			@paymentType,
			@paymentNotes
			)
	END
GO

--Insert New Task Type
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'sp_InsTaskType'
        )
DROP PROCEDURE sp_InsTaskType
GO

CREATE PROCEDURE sp_InsTaskType
            
	@taskTypeName	VARCHAR(50)

	AS
	
	IF NOT EXISTS (SELECT taskTypeID FROM dbo.TASKTYPE WHERE taskTypeName = @taskTypeName)
	BEGIN
		INSERT INTO dbo.[TASKTYPE] (
			taskTypeName
			)
		VALUES
			(
			@taskTypeName
			)
	END
GO

--Insert New Task
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'sp_InsTask'
        )
DROP PROCEDURE sp_InsTask
GO

CREATE PROCEDURE sp_InsTask
            
	@resID			INT,
	@taskName		VARCHAR(100),
	@taskTypeName	VARCHAR(50),
    @taskDueDate	DATETIME,
    @taskComplete	BIT = 0,
	@taskDetails    NVARCHAR(MAX) = NULL,
    
	@taskID INT OUTPUT
	AS
	
	SELECT @taskID = -1;
	
	IF EXISTS (SELECT resID FROM dbo.RESERVATION WHERE resID = @resID)
	BEGIN
	
		IF NOT EXISTS (SELECT taskTypeID FROM dbo.[TASKTYPE] WHERE taskTypeName = @taskTypeName)
		BEGIN
			EXEC sp_InsTaskType @taskTypeName
		END
		
		DECLARE	@taskTypeID int
		SELECT  @taskTypeID = taskTypeID FROM dbo.[TASKTYPE] WHERE taskTypeName = @taskTypeName
		
		INSERT INTO dbo.[TASK] (
			resID,
			taskName,
			taskDueDate,
			taskComplete,
			taskTypeID,
			taskDetails
			)
		VALUES
			(
			@resID,
			@taskName,
			@taskDueDate,
			@taskComplete,
			@taskTypeID,
			@taskDetails
			)
			
		SELECT @taskID = @@identity
	END

	RETURN
GO

--Remove held reservations older than 48 hours
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'sp_RmHeldRes'
        )
DROP PROCEDURE sp_RmHeldRes
GO

CREATE PROCEDURE sp_RmHeldRes
    
	AS

	DECLARE @date DATETIME
	SELECT @date = GETDATE()
	
	DELETE FROM dbo.RESERVATION
	WHERE DATEADD(HOUR, 48, resDate) <= @date
		AND resIsHeld = 1
	
GO

--Purge clients with old reservations 
--AND the client account was created before the number of days specified
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'sp_rmOldClients'
        )
DROP PROCEDURE sp_rmOldClients
GO

CREATE PROCEDURE sp_rmOldClients

	@clientDaysOld int,
    @resDaysOld int
	
	AS
	
	DECLARE @lastResEndDate datetime
	SELECT @lastResEndDate = ( SELECT subq.EndDate FROM 
									(SELECT MAX(r.resEndDate) AS EndDate, c.clientID
									FROM dbo.RESERVATION r JOIN dbo.CLIENT c ON r.clientID = c.clientID
									GROUP BY c.clientID)subq)
						
	DELETE c FROM dbo.CLIENT c 
		WHERE (DATEADD(DAY, @resDaysOld, @lastResEndDate) <= GETDATE() OR @lastResEndDate IS NULL) AND
				DATEADD(DAY, @clientDaysOld, c.clientDateCreated) <= GETDATE()

	
GO

--Trigger to update reservation from held to permanent if the deposit was made.
IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_NAME = 'tr_updateHold'
        )
DROP TRIGGER tr_updateHold
GO

CREATE TRIGGER tr_updateHold
ON dbo.PAYMENT AFTER INSERT
    
	AS
	
	DECLARE @resID INT
	SELECT @resID = (SELECT resID FROM inserted)
	
	IF EXISTS(SELECT * FROM inserted i
				JOIN dbo.PAYMENT p ON i.resID = p.resID
				JOIN dbo.RESERVATION r on r.resID = p.resID
				WHERE r.resIsHeld = 1 
					AND i.paymentIsDeposit = 1)
	BEGIN
		UPDATE dbo.RESERVATION
		SET resIsHeld = 0 
		WHERE @resID = resID
	END
GO

-- Create View for Held Reservations



IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.VIEWS
        WHERE TABLE_NAME = 'RESHELD'
        AND TABLE_CATALOG = 'thailanddb'
        )
DROP VIEW RESHELD
GO

CREATE VIEW RESHELD
AS
	SELECT  resID, resDate, resStartDate, resEndDate, resQuotedPrice, resIsHeld, propID, clientID, userID
	FROM dbo.RESERVATION
	WHERE resIsHeld = 1
GO

-- Create View for Accounting Info

IF EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.VIEWS
        WHERE TABLE_NAME = 'ACCTINFO'
        AND TABLE_CATALOG = 'thailanddb'
        )
DROP VIEW ACCTINFO
GO

CREATE VIEW ACCTINFO
AS
	SELECT  r.resID, clientID, resQuotedPrice, SUM(paymentAmount) AS paid, (resQuotedPrice - SUM(paymentAmount)) AS owed
	FROM dbo.RESERVATION r 
	JOIN dbo.PAYMENT p on r.resID = p.resID
	GROUP BY r.resID, clientID, resQuotedPrice
GO










--------------------------------------------------------------------------------------
--Insert Data
--------------------------------------------------------------------------------------
/*
BULK
INSERT dbo.PERMISSION
FROM 'c:\thailand\csv\PERMISSION.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT [USER]
FROM 'c:\thailand\csv\USER.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT USERPERMISSION
FROM 'c:\thailand\csv\USERPERMISSION.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT PHONE
FROM 'c:\thailand\csv\PHONE.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT COUNTRYCODE
FROM 'c:\thailand\csv\COUNTRYCODE.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT CLIENT
FROM 'c:\thailand\csv\CLIENT.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT CLIENTPHONE
FROM 'c:\thailand\csv\CLIENTPHONE.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT EMAILSENT
FROM 'c:\thailand\csv\EMAILSENT.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT PROPERTY
FROM 'c:\thailand\csv\PROPERTY.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT TASKTYPE
FROM 'c:\thailand\csv\TASKTYPE.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT TASK
FROM 'c:\thailand\csv\EMAILPREFERENCE.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT RESERVATION
FROM 'c:\thailand\csv\RESERVATION.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT PAYMENT
FROM 'c:\thailand\csv\PAYMENT.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

GO
BULK
INSERT COOKIE
FROM 'c:\thailand\csv\COOKIE.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT ERROR
FROM 'c:\thailand\csv\ERROR.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT EMAILPREFERENCE
FROM 'c:\thailand\csv\EMAILPREFERENCE.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
*/

--Insert data into permissions
EXEC sp_InsPerm  @permLevel = 1, @permDetails = 'Full access administrator rights'
EXEC sp_InsPerm  @permLevel = 2, @permDetails = 'Manage reservations, customers, tasks, send e-mail.'
EXEC sp_InsPerm  @permLevel = 3, @permDetails = 'Read-Only'

--Insert data into users and user permissions
EXEC sp_InsUser @userEmail = 'chiangmaiapartmentrentals@gmail.com', @userFirstName = 'Richard', @userLastName = 'Katze', @username = 'richardkatze', @userPassword = 'password1', @permLevel = 1, @userPermDetails = 'Business owner, has full access'
EXEC sp_InsUser @userEmail = 'suzy@fakemail.com',@userFirstName = 'Suzy', @userLastName = 'Mang', @username = 'suzymang', @userPassword = 'password2', @permLevel = 2
EXEC sp_InsUser @userEmail = 'jill@fakemail.com',@userFirstName = 'Jill', @userLastName = 'Robertson', @username = 'jillrobertson', @userPassword = 'password3', @permLevel = 3
EXEC sp_InsUser @userEmail = 'admin@treebellcomputing.com',@userFirstName = 'Cool People', @userLastName = 'Admin', @username = 'admin', @userPassword = 'admin', @permLevel = 1
EXEC sp_InsUser @userEmail = 'jimmywhip@gmail.com',@userFirstName = 'Alex', @userLastName = 'Harris', @username = 'aharris', @userPassword = 'password', @permLevel = 1
EXEC sp_InsUser @userEmail = 'brian@mail.weber.edu',@userFirstName = 'Brian', @userLastName = 'Greene', @username = 'bgreene', @userPassword = 'chargers', @permLevel = 1
EXEC sp_InsUser @userEmail = 'zwilcock@gmail.com',@userFirstName = 'Zach', @userLastName = 'Wilcock', @username = 'zach', @userPassword = 'password', @permLevel = 1

--Insert data into phone type
EXEC sp_InsPhoneType @phoneType = 'Cell'
EXEC sp_InsPhoneType @phoneType = 'Home'
EXEC sp_InsPhoneType @phoneType = 'Work'
EXEC sp_InsPhoneType @phoneType = 'Fax'

--Insert data into country code
EXEC sp_InsCountryCode @ccISO2 = 'AF',@ccISO3 = 'AFG',@ccNumCode = '4',@ccCallingCode = '93',@ccShortName = 'Afghanistan',@ccLongName = 'Islamic Republic of Afghanistan'
EXEC sp_InsCountryCode @ccISO2 = 'AX',@ccISO3 = 'ALA',@ccNumCode = '248',@ccCallingCode = '358',@ccShortName = 'Aland Islands',@ccLongName = '&Aring;land Islands'
EXEC sp_InsCountryCode @ccISO2 = 'AL',@ccISO3 = 'ALB',@ccNumCode = '8',@ccCallingCode = '355',@ccShortName = 'Albania',@ccLongName = 'Republic of Albania'
EXEC sp_InsCountryCode @ccISO2 = 'DZ',@ccISO3 = 'DZA',@ccNumCode = '12',@ccCallingCode = '213',@ccShortName = 'Algeria',@ccLongName = 'People''s Democratic Republic of Algeria'
EXEC sp_InsCountryCode @ccISO2 = 'AS',@ccISO3 = 'ASM',@ccNumCode = '16',@ccCallingCode = '1+684',@ccShortName = 'American Samoa',@ccLongName = 'American Samoa'
EXEC sp_InsCountryCode @ccISO2 = 'AD',@ccISO3 = 'AND',@ccNumCode = '20',@ccCallingCode = '376',@ccShortName = 'Andorra',@ccLongName = 'Principality of Andorra'
EXEC sp_InsCountryCode @ccISO2 = 'AO',@ccISO3 = 'AGO',@ccNumCode = '24',@ccCallingCode = '244',@ccShortName = 'Angola',@ccLongName = 'Republic of Angola'
EXEC sp_InsCountryCode @ccISO2 = 'AI',@ccISO3 = 'AIA',@ccNumCode = '660',@ccCallingCode = '1+264',@ccShortName = 'Anguilla',@ccLongName = 'Anguilla'
EXEC sp_InsCountryCode @ccISO2 = 'AQ',@ccISO3 = 'ATA',@ccNumCode = '10',@ccCallingCode = '672',@ccShortName = 'Antarctica',@ccLongName = 'Antarctica'
EXEC sp_InsCountryCode @ccISO2 = 'AG',@ccISO3 = 'ATG',@ccNumCode = '28',@ccCallingCode = '1+268',@ccShortName = 'Antigua and Barbuda',@ccLongName = 'Antigua and Barbuda'
EXEC sp_InsCountryCode @ccISO2 = 'AR',@ccISO3 = 'ARG',@ccNumCode = '32',@ccCallingCode = '54',@ccShortName = 'Argentina',@ccLongName = 'Argentine Republic'
EXEC sp_InsCountryCode @ccISO2 = 'AM',@ccISO3 = 'ARM',@ccNumCode = '51',@ccCallingCode = '374',@ccShortName = 'Armenia',@ccLongName = 'Republic of Armenia'
EXEC sp_InsCountryCode @ccISO2 = 'AW',@ccISO3 = 'ABW',@ccNumCode = '533',@ccCallingCode = '297',@ccShortName = 'Aruba',@ccLongName = 'Aruba'
EXEC sp_InsCountryCode @ccISO2 = 'AU',@ccISO3 = 'AUS',@ccNumCode = '36',@ccCallingCode = '61',@ccShortName = 'Australia',@ccLongName = 'Commonwealth of Australia'
EXEC sp_InsCountryCode @ccISO2 = 'AT',@ccISO3 = 'AUT',@ccNumCode = '40',@ccCallingCode = '43',@ccShortName = 'Austria',@ccLongName = 'Republic of Austria'
EXEC sp_InsCountryCode @ccISO2 = 'AZ',@ccISO3 = 'AZE',@ccNumCode = '31',@ccCallingCode = '994',@ccShortName = 'Azerbaijan',@ccLongName = 'Republic of Azerbaijan'
EXEC sp_InsCountryCode @ccISO2 = 'BS',@ccISO3 = 'BHS',@ccNumCode = '44',@ccCallingCode = '1+242',@ccShortName = 'Bahamas',@ccLongName = 'Commonwealth of The Bahamas'
EXEC sp_InsCountryCode @ccISO2 = 'BH',@ccISO3 = 'BHR',@ccNumCode = '48',@ccCallingCode = '973',@ccShortName = 'Bahrain',@ccLongName = 'Kingdom of Bahrain'
EXEC sp_InsCountryCode @ccISO2 = 'BD',@ccISO3 = 'BGD',@ccNumCode = '50',@ccCallingCode = '880',@ccShortName = 'Bangladesh',@ccLongName = 'People''s Republic of Bangladesh'
EXEC sp_InsCountryCode @ccISO2 = 'BB',@ccISO3 = 'BRB',@ccNumCode = '52',@ccCallingCode = '1+246',@ccShortName = 'Barbados',@ccLongName = 'Barbados'
EXEC sp_InsCountryCode @ccISO2 = 'BY',@ccISO3 = 'BLR',@ccNumCode = '112',@ccCallingCode = '375',@ccShortName = 'Belarus',@ccLongName = 'Republic of Belarus'
EXEC sp_InsCountryCode @ccISO2 = 'BE',@ccISO3 = 'BEL',@ccNumCode = '56',@ccCallingCode = '32',@ccShortName = 'Belgium',@ccLongName = 'Kingdom of Belgium'
EXEC sp_InsCountryCode @ccISO2 = 'BZ',@ccISO3 = 'BLZ',@ccNumCode = '84',@ccCallingCode = '501',@ccShortName = 'Belize',@ccLongName = 'Belize'
EXEC sp_InsCountryCode @ccISO2 = 'BJ',@ccISO3 = 'BEN',@ccNumCode = '204',@ccCallingCode = '229',@ccShortName = 'Benin',@ccLongName = 'Republic of Benin'
EXEC sp_InsCountryCode @ccISO2 = 'BM',@ccISO3 = 'BMU',@ccNumCode = '60',@ccCallingCode = '1+441',@ccShortName = 'Bermuda',@ccLongName = 'Bermuda Islands'
EXEC sp_InsCountryCode @ccISO2 = 'BT',@ccISO3 = 'BTN',@ccNumCode = '64',@ccCallingCode = '975',@ccShortName = 'Bhutan',@ccLongName = 'Kingdom of Bhutan'
EXEC sp_InsCountryCode @ccISO2 = 'BO',@ccISO3 = 'BOL',@ccNumCode = '68',@ccCallingCode = '591',@ccShortName = 'Bolivia',@ccLongName = 'Plurinational State of Bolivia'
EXEC sp_InsCountryCode @ccISO2 = 'BQ',@ccISO3 = 'BES',@ccNumCode = '535',@ccCallingCode = '599',@ccShortName = 'Bonaire, Sint Eustatius and Saba',@ccLongName = 'Bonaire, Sint Eustatius and Saba'
EXEC sp_InsCountryCode @ccISO2 = 'BA',@ccISO3 = 'BIH',@ccNumCode = '70',@ccCallingCode = '387',@ccShortName = 'Bosnia and Herzegovina',@ccLongName = 'Bosnia and Herzegovina'
EXEC sp_InsCountryCode @ccISO2 = 'BW',@ccISO3 = 'BWA',@ccNumCode = '72',@ccCallingCode = '267',@ccShortName = 'Botswana',@ccLongName = 'Republic of Botswana'
EXEC sp_InsCountryCode @ccISO2 = 'BV',@ccISO3 = 'BVT',@ccNumCode = '74',@ccCallingCode = 'NONE',@ccShortName = 'Bouvet Island',@ccLongName = 'Bouvet Island'
EXEC sp_InsCountryCode @ccISO2 = 'BR',@ccISO3 = 'BRA',@ccNumCode = '76',@ccCallingCode = '55',@ccShortName = 'Brazil',@ccLongName = 'Federative Republic of Brazil'
EXEC sp_InsCountryCode @ccISO2 = 'IO',@ccISO3 = 'IOT',@ccNumCode = '86',@ccCallingCode = '246',@ccShortName = 'British Indian Ocean Territory',@ccLongName = 'British Indian Ocean Territory'
EXEC sp_InsCountryCode @ccISO2 = 'BN',@ccISO3 = 'BRN',@ccNumCode = '96',@ccCallingCode = '673',@ccShortName = 'Brunei',@ccLongName = 'Brunei Darussalam'
EXEC sp_InsCountryCode @ccISO2 = 'BG',@ccISO3 = 'BGR',@ccNumCode = '100',@ccCallingCode = '359',@ccShortName = 'Bulgaria',@ccLongName = 'Republic of Bulgaria'
EXEC sp_InsCountryCode @ccISO2 = 'BF',@ccISO3 = 'BFA',@ccNumCode = '854',@ccCallingCode = '226',@ccShortName = 'Burkina Faso',@ccLongName = 'Burkina Faso'
EXEC sp_InsCountryCode @ccISO2 = 'BI',@ccISO3 = 'BDI',@ccNumCode = '108',@ccCallingCode = '257',@ccShortName = 'Burundi',@ccLongName = 'Republic of Burundi'
EXEC sp_InsCountryCode @ccISO2 = 'KH',@ccISO3 = 'KHM',@ccNumCode = '116',@ccCallingCode = '855',@ccShortName = 'Cambodia',@ccLongName = 'Kingdom of Cambodia'
EXEC sp_InsCountryCode @ccISO2 = 'CM',@ccISO3 = 'CMR',@ccNumCode = '120',@ccCallingCode = '237',@ccShortName = 'Cameroon',@ccLongName = 'Republic of Cameroon'
EXEC sp_InsCountryCode @ccISO2 = 'CA',@ccISO3 = 'CAN',@ccNumCode = '124',@ccCallingCode = '1',@ccShortName = 'Canada',@ccLongName = 'Canada'
EXEC sp_InsCountryCode @ccISO2 = 'CV',@ccISO3 = 'CPV',@ccNumCode = '132',@ccCallingCode = '238',@ccShortName = 'Cape Verde',@ccLongName = 'Republic of Cape Verde'
EXEC sp_InsCountryCode @ccISO2 = 'KY',@ccISO3 = 'CYM',@ccNumCode = '136',@ccCallingCode = '1+345',@ccShortName = 'Cayman Islands',@ccLongName = 'The Cayman Islands'
EXEC sp_InsCountryCode @ccISO2 = 'CF',@ccISO3 = 'CAF',@ccNumCode = '140',@ccCallingCode = '236',@ccShortName = 'Central African Republic',@ccLongName = 'Central African Republic'
EXEC sp_InsCountryCode @ccISO2 = 'TD',@ccISO3 = 'TCD',@ccNumCode = '148',@ccCallingCode = '235',@ccShortName = 'Chad',@ccLongName = 'Republic of Chad'
EXEC sp_InsCountryCode @ccISO2 = 'CL',@ccISO3 = 'CHL',@ccNumCode = '152',@ccCallingCode = '56',@ccShortName = 'Chile',@ccLongName = 'Republic of Chile'
EXEC sp_InsCountryCode @ccISO2 = 'CN',@ccISO3 = 'CHN',@ccNumCode = '156',@ccCallingCode = '86',@ccShortName = 'China',@ccLongName = 'People''s Republic of China'
EXEC sp_InsCountryCode @ccISO2 = 'CX',@ccISO3 = 'CXR',@ccNumCode = '162',@ccCallingCode = '61',@ccShortName = 'Christmas Island',@ccLongName = 'Christmas Island'
EXEC sp_InsCountryCode @ccISO2 = 'CC',@ccISO3 = 'CCK',@ccNumCode = '166',@ccCallingCode = '61',@ccShortName = 'Cocos (Keeling) Islands',@ccLongName = 'Cocos (Keeling) Islands'
EXEC sp_InsCountryCode @ccISO2 = 'CO',@ccISO3 = 'COL',@ccNumCode = '170',@ccCallingCode = '57',@ccShortName = 'Colombia',@ccLongName = 'Republic of Colombia'
EXEC sp_InsCountryCode @ccISO2 = 'KM',@ccISO3 = 'COM',@ccNumCode = '174',@ccCallingCode = '269',@ccShortName = 'Comoros',@ccLongName = 'Union of the Comoros'
EXEC sp_InsCountryCode @ccISO2 = 'CG',@ccISO3 = 'COG',@ccNumCode = '178',@ccCallingCode = '242',@ccShortName = 'Congo',@ccLongName = 'Republic of the Congo'
EXEC sp_InsCountryCode @ccISO2 = 'CK',@ccISO3 = 'COK',@ccNumCode = '184',@ccCallingCode = '682',@ccShortName = 'Cook Islands',@ccLongName = 'Cook Islands'
EXEC sp_InsCountryCode @ccISO2 = 'CR',@ccISO3 = 'CRI',@ccNumCode = '188',@ccCallingCode = '506',@ccShortName = 'Costa Rica',@ccLongName = 'Republic of Costa Rica'
EXEC sp_InsCountryCode @ccISO2 = 'CI',@ccISO3 = 'CIV',@ccNumCode = '384',@ccCallingCode = '225',@ccShortName = 'Cote d''ivoire (Ivory Coast)',@ccLongName = 'Republic of C&ocirc;te D''Ivoire (Ivory Coast)'
EXEC sp_InsCountryCode @ccISO2 = 'HR',@ccISO3 = 'HRV',@ccNumCode = '191',@ccCallingCode = '385',@ccShortName = 'Croatia',@ccLongName = 'Republic of Croatia'
EXEC sp_InsCountryCode @ccISO2 = 'CU',@ccISO3 = 'CUB',@ccNumCode = '192',@ccCallingCode = '53',@ccShortName = 'Cuba',@ccLongName = 'Republic of Cuba'
EXEC sp_InsCountryCode @ccISO2 = 'CW',@ccISO3 = 'CUW',@ccNumCode = '531',@ccCallingCode = '599',@ccShortName = 'Curacao',@ccLongName = 'Cura&ccedil;ao'
EXEC sp_InsCountryCode @ccISO2 = 'CY',@ccISO3 = 'CYP',@ccNumCode = '196',@ccCallingCode = '357',@ccShortName = 'Cyprus',@ccLongName = 'Republic of Cyprus'
EXEC sp_InsCountryCode @ccISO2 = 'CZ',@ccISO3 = 'CZE',@ccNumCode = '203',@ccCallingCode = '420',@ccShortName = 'Czech Republic',@ccLongName = 'Czech Republic'
EXEC sp_InsCountryCode @ccISO2 = 'CD',@ccISO3 = 'COD',@ccNumCode = '180',@ccCallingCode = '243',@ccShortName = 'Democratic Republic of the Congo',@ccLongName = 'Democratic Republic of the Congo'
EXEC sp_InsCountryCode @ccISO2 = 'DK',@ccISO3 = 'DNK',@ccNumCode = '208',@ccCallingCode = '45',@ccShortName = 'Denmark',@ccLongName = 'Kingdom of Denmark'
EXEC sp_InsCountryCode @ccISO2 = 'DJ',@ccISO3 = 'DJI',@ccNumCode = '262',@ccCallingCode = '253',@ccShortName = 'Djibouti',@ccLongName = 'Republic of Djibouti'
EXEC sp_InsCountryCode @ccISO2 = 'DM',@ccISO3 = 'DMA',@ccNumCode = '212',@ccCallingCode = '1+767',@ccShortName = 'Dominica',@ccLongName = 'Commonwealth of Dominica'
EXEC sp_InsCountryCode @ccISO2 = 'DO',@ccISO3 = 'DOM',@ccNumCode = '214',@ccCallingCode = '1+809, 8',@ccShortName = 'Dominican Republic',@ccLongName = 'Dominican Republic'
EXEC sp_InsCountryCode @ccISO2 = 'EC',@ccISO3 = 'ECU',@ccNumCode = '218',@ccCallingCode = '593',@ccShortName = 'Ecuador',@ccLongName = 'Republic of Ecuador'
EXEC sp_InsCountryCode @ccISO2 = 'EG',@ccISO3 = 'EGY',@ccNumCode = '818',@ccCallingCode = '20',@ccShortName = 'Egypt',@ccLongName = 'Arab Republic of Egypt'
EXEC sp_InsCountryCode @ccISO2 = 'SV',@ccISO3 = 'SLV',@ccNumCode = '222',@ccCallingCode = '503',@ccShortName = 'El Salvador',@ccLongName = 'Republic of El Salvador'
EXEC sp_InsCountryCode @ccISO2 = 'GQ',@ccISO3 = 'GNQ',@ccNumCode = '226',@ccCallingCode = '240',@ccShortName = 'Equatorial Guinea',@ccLongName = 'Republic of Equatorial Guinea'
EXEC sp_InsCountryCode @ccISO2 = 'ER',@ccISO3 = 'ERI',@ccNumCode = '232',@ccCallingCode = '291',@ccShortName = 'Eritrea',@ccLongName = 'State of Eritrea'
EXEC sp_InsCountryCode @ccISO2 = 'EE',@ccISO3 = 'EST',@ccNumCode = '233',@ccCallingCode = '372',@ccShortName = 'Estonia',@ccLongName = 'Republic of Estonia'
EXEC sp_InsCountryCode @ccISO2 = 'ET',@ccISO3 = 'ETH',@ccNumCode = '231',@ccCallingCode = '251',@ccShortName = 'Ethiopia',@ccLongName = 'Federal Democratic Republic of Ethiopia'
EXEC sp_InsCountryCode @ccISO2 = 'FK',@ccISO3 = 'FLK',@ccNumCode = '238',@ccCallingCode = '500',@ccShortName = 'Falkland Islands (Malvinas)',@ccLongName = 'The Falkland Islands (Malvinas)'
EXEC sp_InsCountryCode @ccISO2 = 'FO',@ccISO3 = 'FRO',@ccNumCode = '234',@ccCallingCode = '298',@ccShortName = 'Faroe Islands',@ccLongName = 'The Faroe Islands'
EXEC sp_InsCountryCode @ccISO2 = 'FJ',@ccISO3 = 'FJI',@ccNumCode = '242',@ccCallingCode = '679',@ccShortName = 'Fiji',@ccLongName = 'Republic of Fiji'
EXEC sp_InsCountryCode @ccISO2 = 'FI',@ccISO3 = 'FIN',@ccNumCode = '246',@ccCallingCode = '358',@ccShortName = 'Finland',@ccLongName = 'Republic of Finland'
EXEC sp_InsCountryCode @ccISO2 = 'FR',@ccISO3 = 'FRA',@ccNumCode = '250',@ccCallingCode = '33',@ccShortName = 'France',@ccLongName = 'French Republic'
EXEC sp_InsCountryCode @ccISO2 = 'GF',@ccISO3 = 'GUF',@ccNumCode = '254',@ccCallingCode = '594',@ccShortName = 'French Guiana',@ccLongName = 'French Guiana'
EXEC sp_InsCountryCode @ccISO2 = 'PF',@ccISO3 = 'PYF',@ccNumCode = '258',@ccCallingCode = '689',@ccShortName = 'French Polynesia',@ccLongName = 'French Polynesia'
EXEC sp_InsCountryCode @ccISO2 = 'TF',@ccISO3 = 'ATF',@ccNumCode = '260',@ccCallingCode = 'NULL',@ccShortName = 'French Southern Territories',@ccLongName = 'French Southern Territories'
EXEC sp_InsCountryCode @ccISO2 = 'GA',@ccISO3 = 'GAB',@ccNumCode = '266',@ccCallingCode = '241',@ccShortName = 'Gabon',@ccLongName = 'Gabonese Republic'
EXEC sp_InsCountryCode @ccISO2 = 'GM',@ccISO3 = 'GMB',@ccNumCode = '270',@ccCallingCode = '220',@ccShortName = 'Gambia',@ccLongName = 'Republic of The Gambia'
EXEC sp_InsCountryCode @ccISO2 = 'GE',@ccISO3 = 'GEO',@ccNumCode = '268',@ccCallingCode = '995',@ccShortName = 'Georgia',@ccLongName = 'Georgia'
EXEC sp_InsCountryCode @ccISO2 = 'DE',@ccISO3 = 'DEU',@ccNumCode = '276',@ccCallingCode = '49',@ccShortName = 'Germany',@ccLongName = 'Federal Republic of Germany'
EXEC sp_InsCountryCode @ccISO2 = 'GH',@ccISO3 = 'GHA',@ccNumCode = '288',@ccCallingCode = '233',@ccShortName = 'Ghana',@ccLongName = 'Republic of Ghana'
EXEC sp_InsCountryCode @ccISO2 = 'GI',@ccISO3 = 'GIB',@ccNumCode = '292',@ccCallingCode = '350',@ccShortName = 'Gibraltar',@ccLongName = 'Gibraltar'
EXEC sp_InsCountryCode @ccISO2 = 'GR',@ccISO3 = 'GRC',@ccNumCode = '300',@ccCallingCode = '30',@ccShortName = 'Greece',@ccLongName = 'Hellenic Republic'
EXEC sp_InsCountryCode @ccISO2 = 'GL',@ccISO3 = 'GRL',@ccNumCode = '304',@ccCallingCode = '299',@ccShortName = 'Greenland',@ccLongName = 'Greenland'
EXEC sp_InsCountryCode @ccISO2 = 'GD',@ccISO3 = 'GRD',@ccNumCode = '308',@ccCallingCode = '1+473',@ccShortName = 'Grenada',@ccLongName = 'Grenada'
EXEC sp_InsCountryCode @ccISO2 = 'GP',@ccISO3 = 'GLP',@ccNumCode = '312',@ccCallingCode = '590',@ccShortName = 'Guadaloupe',@ccLongName = 'Guadeloupe'
EXEC sp_InsCountryCode @ccISO2 = 'GU',@ccISO3 = 'GUM',@ccNumCode = '316',@ccCallingCode = '1+671',@ccShortName = 'Guam',@ccLongName = 'Guam'
EXEC sp_InsCountryCode @ccISO2 = 'GT',@ccISO3 = 'GTM',@ccNumCode = '320',@ccCallingCode = '502',@ccShortName = 'Guatemala',@ccLongName = 'Republic of Guatemala'
EXEC sp_InsCountryCode @ccISO2 = 'GG',@ccISO3 = 'GGY',@ccNumCode = '831',@ccCallingCode = '44',@ccShortName = 'Guernsey',@ccLongName = 'Guernsey'
EXEC sp_InsCountryCode @ccISO2 = 'GN',@ccISO3 = 'GIN',@ccNumCode = '324',@ccCallingCode = '224',@ccShortName = 'Guinea',@ccLongName = 'Republic of Guinea'
EXEC sp_InsCountryCode @ccISO2 = 'GW',@ccISO3 = 'GNB',@ccNumCode = '624',@ccCallingCode = '245',@ccShortName = 'Guinea-Bissau',@ccLongName = 'Republic of Guinea-Bissau'
EXEC sp_InsCountryCode @ccISO2 = 'GY',@ccISO3 = 'GUY',@ccNumCode = '328',@ccCallingCode = '592',@ccShortName = 'Guyana',@ccLongName = 'Co-operative Republic of Guyana'
EXEC sp_InsCountryCode @ccISO2 = 'HT',@ccISO3 = 'HTI',@ccNumCode = '332',@ccCallingCode = '509',@ccShortName = 'Haiti',@ccLongName = 'Republic of Haiti'
EXEC sp_InsCountryCode @ccISO2 = 'HM',@ccISO3 = 'HMD',@ccNumCode = '334',@ccCallingCode = 'NONE',@ccShortName = 'Heard Island and McDonald Islands',@ccLongName = 'Heard Island and McDonald Islands'
EXEC sp_InsCountryCode @ccISO2 = 'HN',@ccISO3 = 'HND',@ccNumCode = '340',@ccCallingCode = '504',@ccShortName = 'Honduras',@ccLongName = 'Republic of Honduras'
EXEC sp_InsCountryCode @ccISO2 = 'HK',@ccISO3 = 'HKG',@ccNumCode = '344',@ccCallingCode = '852',@ccShortName = 'Hong Kong',@ccLongName = 'Hong Kong'
EXEC sp_InsCountryCode @ccISO2 = 'HU',@ccISO3 = 'HUN',@ccNumCode = '348',@ccCallingCode = '36',@ccShortName = 'Hungary',@ccLongName = 'Hungary'
EXEC sp_InsCountryCode @ccISO2 = 'IS',@ccISO3 = 'ISL',@ccNumCode = '352',@ccCallingCode = '354',@ccShortName = 'Iceland',@ccLongName = 'Republic of Iceland'
EXEC sp_InsCountryCode @ccISO2 = 'IN',@ccISO3 = 'IND',@ccNumCode = '356',@ccCallingCode = '91',@ccShortName = 'India',@ccLongName = 'Republic of India'
EXEC sp_InsCountryCode @ccISO2 = 'ID',@ccISO3 = 'IDN',@ccNumCode = '360',@ccCallingCode = '62',@ccShortName = 'Indonesia',@ccLongName = 'Republic of Indonesia'
EXEC sp_InsCountryCode @ccISO2 = 'IR',@ccISO3 = 'IRN',@ccNumCode = '364',@ccCallingCode = '98',@ccShortName = 'Iran',@ccLongName = 'Islamic Republic of Iran'
EXEC sp_InsCountryCode @ccISO2 = 'IQ',@ccISO3 = 'IRQ',@ccNumCode = '368',@ccCallingCode = '964',@ccShortName = 'Iraq',@ccLongName = 'Republic of Iraq'
EXEC sp_InsCountryCode @ccISO2 = 'IE',@ccISO3 = 'IRL',@ccNumCode = '372',@ccCallingCode = '353',@ccShortName = 'Ireland',@ccLongName = 'Ireland'
EXEC sp_InsCountryCode @ccISO2 = 'IM',@ccISO3 = 'IMN',@ccNumCode = '833',@ccCallingCode = '44',@ccShortName = 'Isle of Man',@ccLongName = 'Isle of Man'
EXEC sp_InsCountryCode @ccISO2 = 'IL',@ccISO3 = 'ISR',@ccNumCode = '376',@ccCallingCode = '972',@ccShortName = 'Israel',@ccLongName = 'State of Israel'
EXEC sp_InsCountryCode @ccISO2 = 'IT',@ccISO3 = 'ITA',@ccNumCode = '380',@ccCallingCode = '39',@ccShortName = 'Italy',@ccLongName = 'Italian Republic'
EXEC sp_InsCountryCode @ccISO2 = 'JM',@ccISO3 = 'JAM',@ccNumCode = '388',@ccCallingCode = '1+876',@ccShortName = 'Jamaica',@ccLongName = 'Jamaica'
EXEC sp_InsCountryCode @ccISO2 = 'JP',@ccISO3 = 'JPN',@ccNumCode = '392',@ccCallingCode = '81',@ccShortName = 'Japan',@ccLongName = 'Japan'
EXEC sp_InsCountryCode @ccISO2 = 'JE',@ccISO3 = 'JEY',@ccNumCode = '832',@ccCallingCode = '44',@ccShortName = 'Jersey',@ccLongName = 'The Bailiwick of Jersey'
EXEC sp_InsCountryCode @ccISO2 = 'JO',@ccISO3 = 'JOR',@ccNumCode = '400',@ccCallingCode = '962',@ccShortName = 'Jordan',@ccLongName = 'Hashemite Kingdom of Jordan'
EXEC sp_InsCountryCode @ccISO2 = 'KZ',@ccISO3 = 'KAZ',@ccNumCode = '398',@ccCallingCode = '7',@ccShortName = 'Kazakhstan',@ccLongName = 'Republic of Kazakhstan'
EXEC sp_InsCountryCode @ccISO2 = 'KE',@ccISO3 = 'KEN',@ccNumCode = '404',@ccCallingCode = '254',@ccShortName = 'Kenya',@ccLongName = 'Republic of Kenya'
EXEC sp_InsCountryCode @ccISO2 = 'KI',@ccISO3 = 'KIR',@ccNumCode = '296',@ccCallingCode = '686',@ccShortName = 'Kiribati',@ccLongName = 'Republic of Kiribati'
EXEC sp_InsCountryCode @ccISO2 = 'XK',@ccISO3 = '---',@ccNumCode = '---',@ccCallingCode = '381',@ccShortName = 'Kosovo',@ccLongName = 'Republic of Kosovo'
EXEC sp_InsCountryCode @ccISO2 = 'KW',@ccISO3 = 'KWT',@ccNumCode = '414',@ccCallingCode = '965',@ccShortName = 'Kuwait',@ccLongName = 'State of Kuwait'
EXEC sp_InsCountryCode @ccISO2 = 'KG',@ccISO3 = 'KGZ',@ccNumCode = '417',@ccCallingCode = '996',@ccShortName = 'Kyrgyzstan',@ccLongName = 'Kyrgyz Republic'
EXEC sp_InsCountryCode @ccISO2 = 'LA',@ccISO3 = 'LAO',@ccNumCode = '418',@ccCallingCode = '856',@ccShortName = 'Laos',@ccLongName = 'Lao People''s Democratic Republic'
EXEC sp_InsCountryCode @ccISO2 = 'LV',@ccISO3 = 'LVA',@ccNumCode = '428',@ccCallingCode = '371',@ccShortName = 'Latvia',@ccLongName = 'Republic of Latvia'
EXEC sp_InsCountryCode @ccISO2 = 'LB',@ccISO3 = 'LBN',@ccNumCode = '422',@ccCallingCode = '961',@ccShortName = 'Lebanon',@ccLongName = 'Republic of Lebanon'
EXEC sp_InsCountryCode @ccISO2 = 'LS',@ccISO3 = 'LSO',@ccNumCode = '426',@ccCallingCode = '266',@ccShortName = 'Lesotho',@ccLongName = 'Kingdom of Lesotho'
EXEC sp_InsCountryCode @ccISO2 = 'LR',@ccISO3 = 'LBR',@ccNumCode = '430',@ccCallingCode = '231',@ccShortName = 'Liberia',@ccLongName = 'Republic of Liberia'
EXEC sp_InsCountryCode @ccISO2 = 'LY',@ccISO3 = 'LBY',@ccNumCode = '434',@ccCallingCode = '218',@ccShortName = 'Libya',@ccLongName = 'Libya'
EXEC sp_InsCountryCode @ccISO2 = 'LI',@ccISO3 = 'LIE',@ccNumCode = '438',@ccCallingCode = '423',@ccShortName = 'Liechtenstein',@ccLongName = 'Principality of Liechtenstein'
EXEC sp_InsCountryCode @ccISO2 = 'LT',@ccISO3 = 'LTU',@ccNumCode = '440',@ccCallingCode = '370',@ccShortName = 'Lithuania',@ccLongName = 'Republic of Lithuania'
EXEC sp_InsCountryCode @ccISO2 = 'LU',@ccISO3 = 'LUX',@ccNumCode = '442',@ccCallingCode = '352',@ccShortName = 'Luxembourg',@ccLongName = 'Grand Duchy of Luxembourg'
EXEC sp_InsCountryCode @ccISO2 = 'MO',@ccISO3 = 'MAC',@ccNumCode = '446',@ccCallingCode = '853',@ccShortName = 'Macao',@ccLongName = 'The Macao Special Administrative Region'
EXEC sp_InsCountryCode @ccISO2 = 'MK',@ccISO3 = 'MKD',@ccNumCode = '807',@ccCallingCode = '389',@ccShortName = 'Macedonia',@ccLongName = 'The Former Yugoslav Republic of Macedonia'
EXEC sp_InsCountryCode @ccISO2 = 'MG',@ccISO3 = 'MDG',@ccNumCode = '450',@ccCallingCode = '261',@ccShortName = 'Madagascar',@ccLongName = 'Republic of Madagascar'
EXEC sp_InsCountryCode @ccISO2 = 'MW',@ccISO3 = 'MWI',@ccNumCode = '454',@ccCallingCode = '265',@ccShortName = 'Malawi',@ccLongName = 'Republic of Malawi'
EXEC sp_InsCountryCode @ccISO2 = 'MY',@ccISO3 = 'MYS',@ccNumCode = '458',@ccCallingCode = '60',@ccShortName = 'Malaysia',@ccLongName = 'Malaysia'
EXEC sp_InsCountryCode @ccISO2 = 'MV',@ccISO3 = 'MDV',@ccNumCode = '462',@ccCallingCode = '960',@ccShortName = 'Maldives',@ccLongName = 'Republic of Maldives'
EXEC sp_InsCountryCode @ccISO2 = 'ML',@ccISO3 = 'MLI',@ccNumCode = '466',@ccCallingCode = '223',@ccShortName = 'Mali',@ccLongName = 'Republic of Mali'
EXEC sp_InsCountryCode @ccISO2 = 'MT',@ccISO3 = 'MLT',@ccNumCode = '470',@ccCallingCode = '356',@ccShortName = 'Malta',@ccLongName = 'Republic of Malta'
EXEC sp_InsCountryCode @ccISO2 = 'MH',@ccISO3 = 'MHL',@ccNumCode = '584',@ccCallingCode = '692',@ccShortName = 'Marshall Islands',@ccLongName = 'Republic of the Marshall Islands'
EXEC sp_InsCountryCode @ccISO2 = 'MQ',@ccISO3 = 'MTQ',@ccNumCode = '474',@ccCallingCode = '596',@ccShortName = 'Martinique',@ccLongName = 'Martinique'
EXEC sp_InsCountryCode @ccISO2 = 'MR',@ccISO3 = 'MRT',@ccNumCode = '478',@ccCallingCode = '222',@ccShortName = 'Mauritania',@ccLongName = 'Islamic Republic of Mauritania'
EXEC sp_InsCountryCode @ccISO2 = 'MU',@ccISO3 = 'MUS',@ccNumCode = '480',@ccCallingCode = '230',@ccShortName = 'Mauritius',@ccLongName = 'Republic of Mauritius'
EXEC sp_InsCountryCode @ccISO2 = 'YT',@ccISO3 = 'MYT',@ccNumCode = '175',@ccCallingCode = '262',@ccShortName = 'Mayotte',@ccLongName = 'Mayotte'
EXEC sp_InsCountryCode @ccISO2 = 'MX',@ccISO3 = 'MEX',@ccNumCode = '484',@ccCallingCode = '52',@ccShortName = 'Mexico',@ccLongName = 'United Mexican States'
EXEC sp_InsCountryCode @ccISO2 = 'FM',@ccISO3 = 'FSM',@ccNumCode = '583',@ccCallingCode = '691',@ccShortName = 'Micronesia',@ccLongName = 'Federated States of Micronesia'
EXEC sp_InsCountryCode @ccISO2 = 'MD',@ccISO3 = 'MDA',@ccNumCode = '498',@ccCallingCode = '373',@ccShortName = 'Moldava',@ccLongName = 'Republic of Moldova'
EXEC sp_InsCountryCode @ccISO2 = 'MC',@ccISO3 = 'MCO',@ccNumCode = '492',@ccCallingCode = '377',@ccShortName = 'Monaco',@ccLongName = 'Principality of Monaco'
EXEC sp_InsCountryCode @ccISO2 = 'MN',@ccISO3 = 'MNG',@ccNumCode = '496',@ccCallingCode = '976',@ccShortName = 'Mongolia',@ccLongName = 'Mongolia'
EXEC sp_InsCountryCode @ccISO2 = 'ME',@ccISO3 = 'MNE',@ccNumCode = '499',@ccCallingCode = '382',@ccShortName = 'Montenegro',@ccLongName = 'Montenegro'
EXEC sp_InsCountryCode @ccISO2 = 'MS',@ccISO3 = 'MSR',@ccNumCode = '500',@ccCallingCode = '1+664',@ccShortName = 'Montserrat',@ccLongName = 'Montserrat'
EXEC sp_InsCountryCode @ccISO2 = 'MA',@ccISO3 = 'MAR',@ccNumCode = '504',@ccCallingCode = '212',@ccShortName = 'Morocco',@ccLongName = 'Kingdom of Morocco'
EXEC sp_InsCountryCode @ccISO2 = 'MZ',@ccISO3 = 'MOZ',@ccNumCode = '508',@ccCallingCode = '258',@ccShortName = 'Mozambique',@ccLongName = 'Republic of Mozambique'
EXEC sp_InsCountryCode @ccISO2 = 'MM',@ccISO3 = 'MMR',@ccNumCode = '104',@ccCallingCode = '95',@ccShortName = 'Myanmar (Burma)',@ccLongName = 'Republic of the Union of Myanmar'
EXEC sp_InsCountryCode @ccISO2 = 'NA',@ccISO3 = 'NAM',@ccNumCode = '516',@ccCallingCode = '264',@ccShortName = 'Namibia',@ccLongName = 'Republic of Namibia'
EXEC sp_InsCountryCode @ccISO2 = 'NR',@ccISO3 = 'NRU',@ccNumCode = '520',@ccCallingCode = '674',@ccShortName = 'Nauru',@ccLongName = 'Republic of Nauru'
EXEC sp_InsCountryCode @ccISO2 = 'NP',@ccISO3 = 'NPL',@ccNumCode = '524',@ccCallingCode = '977',@ccShortName = 'Nepal',@ccLongName = 'Federal Democratic Republic of Nepal'
EXEC sp_InsCountryCode @ccISO2 = 'NL',@ccISO3 = 'NLD',@ccNumCode = '528',@ccCallingCode = '31',@ccShortName = 'Netherlands',@ccLongName = 'Kingdom of the Netherlands'
EXEC sp_InsCountryCode @ccISO2 = 'NC',@ccISO3 = 'NCL',@ccNumCode = '540',@ccCallingCode = '687',@ccShortName = 'New Caledonia',@ccLongName = 'New Caledonia'
EXEC sp_InsCountryCode @ccISO2 = 'NZ',@ccISO3 = 'NZL',@ccNumCode = '554',@ccCallingCode = '64',@ccShortName = 'New Zealand',@ccLongName = 'New Zealand'
EXEC sp_InsCountryCode @ccISO2 = 'NI',@ccISO3 = 'NIC',@ccNumCode = '558',@ccCallingCode = '505',@ccShortName = 'Nicaragua',@ccLongName = 'Republic of Nicaragua'
EXEC sp_InsCountryCode @ccISO2 = 'NE',@ccISO3 = 'NER',@ccNumCode = '562',@ccCallingCode = '227',@ccShortName = 'Niger',@ccLongName = 'Republic of Niger'
EXEC sp_InsCountryCode @ccISO2 = 'NG',@ccISO3 = 'NGA',@ccNumCode = '566',@ccCallingCode = '234',@ccShortName = 'Nigeria',@ccLongName = 'Federal Republic of Nigeria'
EXEC sp_InsCountryCode @ccISO2 = 'NU',@ccISO3 = 'NIU',@ccNumCode = '570',@ccCallingCode = '683',@ccShortName = 'Niue',@ccLongName = 'Niue'
EXEC sp_InsCountryCode @ccISO2 = 'NF',@ccISO3 = 'NFK',@ccNumCode = '574',@ccCallingCode = '672',@ccShortName = 'Norfolk Island',@ccLongName = 'Norfolk Island'
EXEC sp_InsCountryCode @ccISO2 = 'KP',@ccISO3 = 'PRK',@ccNumCode = '408',@ccCallingCode = '850',@ccShortName = 'North Korea',@ccLongName = 'Democratic People''s Republic of Korea'
EXEC sp_InsCountryCode @ccISO2 = 'MP',@ccISO3 = 'MNP',@ccNumCode = '580',@ccCallingCode = '1+670',@ccShortName = 'Northern Mariana Islands',@ccLongName = 'Northern Mariana Islands'
EXEC sp_InsCountryCode @ccISO2 = 'NO',@ccISO3 = 'NOR',@ccNumCode = '578',@ccCallingCode = '47',@ccShortName = 'Norway',@ccLongName = 'Kingdom of Norway'
EXEC sp_InsCountryCode @ccISO2 = 'OM',@ccISO3 = 'OMN',@ccNumCode = '512',@ccCallingCode = '968',@ccShortName = 'Oman',@ccLongName = 'Sultanate of Oman'
EXEC sp_InsCountryCode @ccISO2 = 'PK',@ccISO3 = 'PAK',@ccNumCode = '586',@ccCallingCode = '92',@ccShortName = 'Pakistan',@ccLongName = 'Islamic Republic of Pakistan'
EXEC sp_InsCountryCode @ccISO2 = 'PW',@ccISO3 = 'PLW',@ccNumCode = '585',@ccCallingCode = '680',@ccShortName = 'Palau',@ccLongName = 'Republic of Palau'
EXEC sp_InsCountryCode @ccISO2 = 'PS',@ccISO3 = 'PSE',@ccNumCode = '275',@ccCallingCode = '970',@ccShortName = 'Palestine',@ccLongName = 'State of Palestine (or Occupied Palestinian Territory)'
EXEC sp_InsCountryCode @ccISO2 = 'PA',@ccISO3 = 'PAN',@ccNumCode = '591',@ccCallingCode = '507',@ccShortName = 'Panama',@ccLongName = 'Republic of Panama'
EXEC sp_InsCountryCode @ccISO2 = 'PG',@ccISO3 = 'PNG',@ccNumCode = '598',@ccCallingCode = '675',@ccShortName = 'Papua New Guinea',@ccLongName = 'Independent State of Papua New Guinea'
EXEC sp_InsCountryCode @ccISO2 = 'PY',@ccISO3 = 'PRY',@ccNumCode = '600',@ccCallingCode = '595',@ccShortName = 'Paraguay',@ccLongName = 'Republic of Paraguay'
EXEC sp_InsCountryCode @ccISO2 = 'PE',@ccISO3 = 'PER',@ccNumCode = '604',@ccCallingCode = '51',@ccShortName = 'Peru',@ccLongName = 'Republic of Peru'
EXEC sp_InsCountryCode @ccISO2 = 'PH',@ccISO3 = 'PHL',@ccNumCode = '608',@ccCallingCode = '63',@ccShortName = 'Phillipines',@ccLongName = 'Republic of the Philippines'
EXEC sp_InsCountryCode @ccISO2 = 'PN',@ccISO3 = 'PCN',@ccNumCode = '612',@ccCallingCode = 'NONE',@ccShortName = 'Pitcairn',@ccLongName = 'Pitcairn'
EXEC sp_InsCountryCode @ccISO2 = 'PL',@ccISO3 = 'POL',@ccNumCode = '616',@ccCallingCode = '48',@ccShortName = 'Poland',@ccLongName = 'Republic of Poland'
EXEC sp_InsCountryCode @ccISO2 = 'PT',@ccISO3 = 'PRT',@ccNumCode = '620',@ccCallingCode = '351',@ccShortName = 'Portugal',@ccLongName = 'Portuguese Republic'
EXEC sp_InsCountryCode @ccISO2 = 'PR',@ccISO3 = 'PRI',@ccNumCode = '630',@ccCallingCode = '1+939',@ccShortName = 'Puerto Rico',@ccLongName = 'Commonwealth of Puerto Rico'
EXEC sp_InsCountryCode @ccISO2 = 'QA',@ccISO3 = 'QAT',@ccNumCode = '634',@ccCallingCode = '974',@ccShortName = 'Qatar',@ccLongName = 'State of Qatar'
EXEC sp_InsCountryCode @ccISO2 = 'RE',@ccISO3 = 'REU',@ccNumCode = '638',@ccCallingCode = '262',@ccShortName = 'Reunion',@ccLongName = 'R&eacute;union'
EXEC sp_InsCountryCode @ccISO2 = 'RO',@ccISO3 = 'ROU',@ccNumCode = '642',@ccCallingCode = '40',@ccShortName = 'Romania',@ccLongName = 'Romania'
EXEC sp_InsCountryCode @ccISO2 = 'RU',@ccISO3 = 'RUS',@ccNumCode = '643',@ccCallingCode = '7',@ccShortName = 'Russia',@ccLongName = 'Russian Federation'
EXEC sp_InsCountryCode @ccISO2 = 'RW',@ccISO3 = 'RWA',@ccNumCode = '646',@ccCallingCode = '250',@ccShortName = 'Rwanda',@ccLongName = 'Republic of Rwanda'
EXEC sp_InsCountryCode @ccISO2 = 'BL',@ccISO3 = 'BLM',@ccNumCode = '652',@ccCallingCode = '590',@ccShortName = 'Saint Barthelemy',@ccLongName = 'Saint Barth&eacute;lemy'
EXEC sp_InsCountryCode @ccISO2 = 'SH',@ccISO3 = 'SHN',@ccNumCode = '654',@ccCallingCode = '290',@ccShortName = 'Saint Helena',@ccLongName = 'Saint Helena, Ascension and Tristan da Cunha'
EXEC sp_InsCountryCode @ccISO2 = 'KN',@ccISO3 = 'KNA',@ccNumCode = '659',@ccCallingCode = '1+869',@ccShortName = 'Saint Kitts and Nevis',@ccLongName = 'Federation of Saint Christopher and Nevis'
EXEC sp_InsCountryCode @ccISO2 = 'LC',@ccISO3 = 'LCA',@ccNumCode = '662',@ccCallingCode = '1+758',@ccShortName = 'Saint Lucia',@ccLongName = 'Saint Lucia'
EXEC sp_InsCountryCode @ccISO2 = 'MF',@ccISO3 = 'MAF',@ccNumCode = '663',@ccCallingCode = '590',@ccShortName = 'Saint Martin',@ccLongName = 'Saint Martin'
EXEC sp_InsCountryCode @ccISO2 = 'PM',@ccISO3 = 'SPM',@ccNumCode = '666',@ccCallingCode = '508',@ccShortName = 'Saint Pierre and Miquelon',@ccLongName = 'Saint Pierre and Miquelon'
EXEC sp_InsCountryCode @ccISO2 = 'VC',@ccISO3 = 'VCT',@ccNumCode = '670',@ccCallingCode = '1+784',@ccShortName = 'Saint Vincent and the Grenadines',@ccLongName = 'Saint Vincent and the Grenadines'
EXEC sp_InsCountryCode @ccISO2 = 'WS',@ccISO3 = 'WSM',@ccNumCode = '882',@ccCallingCode = '685',@ccShortName = 'Samoa',@ccLongName = 'Independent State of Samoa'
EXEC sp_InsCountryCode @ccISO2 = 'SM',@ccISO3 = 'SMR',@ccNumCode = '674',@ccCallingCode = '378',@ccShortName = 'San Marino',@ccLongName = 'Republic of San Marino'
EXEC sp_InsCountryCode @ccISO2 = 'ST',@ccISO3 = 'STP',@ccNumCode = '678',@ccCallingCode = '239',@ccShortName = 'Sao Tome and Principe',@ccLongName = 'Democratic Republic of S&atilde;o Tom&eacute; and Pr&iacute;ncipe'
EXEC sp_InsCountryCode @ccISO2 = 'SA',@ccISO3 = 'SAU',@ccNumCode = '682',@ccCallingCode = '966',@ccShortName = 'Saudi Arabia',@ccLongName = 'Kingdom of Saudi Arabia'
EXEC sp_InsCountryCode @ccISO2 = 'SN',@ccISO3 = 'SEN',@ccNumCode = '686',@ccCallingCode = '221',@ccShortName = 'Senegal',@ccLongName = 'Republic of Senegal'
EXEC sp_InsCountryCode @ccISO2 = 'RS',@ccISO3 = 'SRB',@ccNumCode = '688',@ccCallingCode = '381',@ccShortName = 'Serbia',@ccLongName = 'Republic of Serbia'
EXEC sp_InsCountryCode @ccISO2 = 'SC',@ccISO3 = 'SYC',@ccNumCode = '690',@ccCallingCode = '248',@ccShortName = 'Seychelles',@ccLongName = 'Republic of Seychelles'
EXEC sp_InsCountryCode @ccISO2 = 'SL',@ccISO3 = 'SLE',@ccNumCode = '694',@ccCallingCode = '232',@ccShortName = 'Sierra Leone',@ccLongName = 'Republic of Sierra Leone'
EXEC sp_InsCountryCode @ccISO2 = 'SG',@ccISO3 = 'SGP',@ccNumCode = '702',@ccCallingCode = '65',@ccShortName = 'Singapore',@ccLongName = 'Republic of Singapore'
EXEC sp_InsCountryCode @ccISO2 = 'SX',@ccISO3 = 'SXM',@ccNumCode = '534',@ccCallingCode = '1+721',@ccShortName = 'Sint Maarten',@ccLongName = 'Sint Maarten'
EXEC sp_InsCountryCode @ccISO2 = 'SK',@ccISO3 = 'SVK',@ccNumCode = '703',@ccCallingCode = '421',@ccShortName = 'Slovakia',@ccLongName = 'Slovak Republic'
EXEC sp_InsCountryCode @ccISO2 = 'SI',@ccISO3 = 'SVN',@ccNumCode = '705',@ccCallingCode = '386',@ccShortName = 'Slovenia',@ccLongName = 'Republic of Slovenia'
EXEC sp_InsCountryCode @ccISO2 = 'SB',@ccISO3 = 'SLB',@ccNumCode = '90',@ccCallingCode = '677',@ccShortName = 'Solomon Islands',@ccLongName = 'Solomon Islands'
EXEC sp_InsCountryCode @ccISO2 = 'SO',@ccISO3 = 'SOM',@ccNumCode = '706',@ccCallingCode = '252',@ccShortName = 'Somalia',@ccLongName = 'Somali Republic'
EXEC sp_InsCountryCode @ccISO2 = 'ZA',@ccISO3 = 'ZAF',@ccNumCode = '710',@ccCallingCode = '27',@ccShortName = 'South Africa',@ccLongName = 'Republic of South Africa'
EXEC sp_InsCountryCode @ccISO2 = 'GS',@ccISO3 = 'SGS',@ccNumCode = '239',@ccCallingCode = '500',@ccShortName = 'South Georgia and the South Sandwich Islands',@ccLongName = 'South Georgia and the South Sandwich Islands'
EXEC sp_InsCountryCode @ccISO2 = 'KR',@ccISO3 = 'KOR',@ccNumCode = '410',@ccCallingCode = '82',@ccShortName = 'South Korea',@ccLongName = 'Republic of Korea'
EXEC sp_InsCountryCode @ccISO2 = 'SS',@ccISO3 = 'SSD',@ccNumCode = '728',@ccCallingCode = '211',@ccShortName = 'South Sudan',@ccLongName = 'Republic of South Sudan'
EXEC sp_InsCountryCode @ccISO2 = 'ES',@ccISO3 = 'ESP',@ccNumCode = '724',@ccCallingCode = '34',@ccShortName = 'Spain',@ccLongName = 'Kingdom of Spain'
EXEC sp_InsCountryCode @ccISO2 = 'LK',@ccISO3 = 'LKA',@ccNumCode = '144',@ccCallingCode = '94',@ccShortName = 'Sri Lanka',@ccLongName = 'Democratic Socialist Republic of Sri Lanka'
EXEC sp_InsCountryCode @ccISO2 = 'SD',@ccISO3 = 'SDN',@ccNumCode = '729',@ccCallingCode = '249',@ccShortName = 'Sudan',@ccLongName = 'Republic of the Sudan'
EXEC sp_InsCountryCode @ccISO2 = 'SR',@ccISO3 = 'SUR',@ccNumCode = '740',@ccCallingCode = '597',@ccShortName = 'Suriname',@ccLongName = 'Republic of Suriname'
EXEC sp_InsCountryCode @ccISO2 = 'SJ',@ccISO3 = 'SJM',@ccNumCode = '744',@ccCallingCode = '47',@ccShortName = 'Svalbard and Jan Mayen',@ccLongName = 'Svalbard and Jan Mayen'
EXEC sp_InsCountryCode @ccISO2 = 'SZ',@ccISO3 = 'SWZ',@ccNumCode = '748',@ccCallingCode = '268',@ccShortName = 'Swaziland',@ccLongName = 'Kingdom of Swaziland'
EXEC sp_InsCountryCode @ccISO2 = 'SE',@ccISO3 = 'SWE',@ccNumCode = '752',@ccCallingCode = '46',@ccShortName = 'Sweden',@ccLongName = 'Kingdom of Sweden'
EXEC sp_InsCountryCode @ccISO2 = 'CH',@ccISO3 = 'CHE',@ccNumCode = '756',@ccCallingCode = '41',@ccShortName = 'Switzerland',@ccLongName = 'Swiss Confederation'
EXEC sp_InsCountryCode @ccISO2 = 'SY',@ccISO3 = 'SYR',@ccNumCode = '760',@ccCallingCode = '963',@ccShortName = 'Syria',@ccLongName = 'Syrian Arab Republic'
EXEC sp_InsCountryCode @ccISO2 = 'TW',@ccISO3 = 'TWN',@ccNumCode = '158',@ccCallingCode = '886',@ccShortName = 'Taiwan',@ccLongName = 'Republic of China (Taiwan)'
EXEC sp_InsCountryCode @ccISO2 = 'TJ',@ccISO3 = 'TJK',@ccNumCode = '762',@ccCallingCode = '992',@ccShortName = 'Tajikistan',@ccLongName = 'Republic of Tajikistan'
EXEC sp_InsCountryCode @ccISO2 = 'TZ',@ccISO3 = 'TZA',@ccNumCode = '834',@ccCallingCode = '255',@ccShortName = 'Tanzania',@ccLongName = 'United Republic of Tanzania'
EXEC sp_InsCountryCode @ccISO2 = 'TH',@ccISO3 = 'THA',@ccNumCode = '764',@ccCallingCode = '66',@ccShortName = 'Thailand',@ccLongName = 'Kingdom of Thailand'
EXEC sp_InsCountryCode @ccISO2 = 'TL',@ccISO3 = 'TLS',@ccNumCode = '626',@ccCallingCode = '670',@ccShortName = 'Timor-Leste (East Timor)',@ccLongName = 'Democratic Republic of Timor-Leste'
EXEC sp_InsCountryCode @ccISO2 = 'TG',@ccISO3 = 'TGO',@ccNumCode = '768',@ccCallingCode = '228',@ccShortName = 'Togo',@ccLongName = 'Togolese Republic'
EXEC sp_InsCountryCode @ccISO2 = 'TK',@ccISO3 = 'TKL',@ccNumCode = '772',@ccCallingCode = '690',@ccShortName = 'Tokelau',@ccLongName = 'Tokelau'
EXEC sp_InsCountryCode @ccISO2 = 'TO',@ccISO3 = 'TON',@ccNumCode = '776',@ccCallingCode = '676',@ccShortName = 'Tonga',@ccLongName = 'Kingdom of Tonga'
EXEC sp_InsCountryCode @ccISO2 = 'TT',@ccISO3 = 'TTO',@ccNumCode = '780',@ccCallingCode = '1+868',@ccShortName = 'Trinidad and Tobago',@ccLongName = 'Republic of Trinidad and Tobago'
EXEC sp_InsCountryCode @ccISO2 = 'TN',@ccISO3 = 'TUN',@ccNumCode = '788',@ccCallingCode = '216',@ccShortName = 'Tunisia',@ccLongName = 'Republic of Tunisia'
EXEC sp_InsCountryCode @ccISO2 = 'TR',@ccISO3 = 'TUR',@ccNumCode = '792',@ccCallingCode = '90',@ccShortName = 'Turkey',@ccLongName = 'Republic of Turkey'
EXEC sp_InsCountryCode @ccISO2 = 'TM',@ccISO3 = 'TKM',@ccNumCode = '795',@ccCallingCode = '993',@ccShortName = 'Turkmenistan',@ccLongName = 'Turkmenistan'
EXEC sp_InsCountryCode @ccISO2 = 'TC',@ccISO3 = 'TCA',@ccNumCode = '796',@ccCallingCode = '1+649',@ccShortName = 'Turks and Caicos Islands',@ccLongName = 'Turks and Caicos Islands'
EXEC sp_InsCountryCode @ccISO2 = 'TV',@ccISO3 = 'TUV',@ccNumCode = '798',@ccCallingCode = '688',@ccShortName = 'Tuvalu',@ccLongName = 'Tuvalu'
EXEC sp_InsCountryCode @ccISO2 = 'UG',@ccISO3 = 'UGA',@ccNumCode = '800',@ccCallingCode = '256',@ccShortName = 'Uganda',@ccLongName = 'Republic of Uganda'
EXEC sp_InsCountryCode @ccISO2 = 'UA',@ccISO3 = 'UKR',@ccNumCode = '804',@ccCallingCode = '380',@ccShortName = 'Ukraine',@ccLongName = 'Ukraine'
EXEC sp_InsCountryCode @ccISO2 = 'AE',@ccISO3 = 'ARE',@ccNumCode = '784',@ccCallingCode = '971',@ccShortName = 'United Arab Emirates',@ccLongName = 'United Arab Emirates'
EXEC sp_InsCountryCode @ccISO2 = 'GB',@ccISO3 = 'GBR',@ccNumCode = '826',@ccCallingCode = '44',@ccShortName = 'United Kingdom',@ccLongName = 'United Kingdom of Great Britain and Nothern Ireland'
EXEC sp_InsCountryCode @ccISO2 = 'US',@ccISO3 = 'USA',@ccNumCode = '840',@ccCallingCode = '1',@ccShortName = 'United States',@ccLongName = 'United States of America'
EXEC sp_InsCountryCode @ccISO2 = 'UM',@ccISO3 = 'UMI',@ccNumCode = '581',@ccCallingCode = 'NONE',@ccShortName = 'United States Minor Outlying Islands',@ccLongName = 'United States Minor Outlying Islands'
EXEC sp_InsCountryCode @ccISO2 = 'UY',@ccISO3 = 'URY',@ccNumCode = '858',@ccCallingCode = '598',@ccShortName = 'Uruguay',@ccLongName = 'Eastern Republic of Uruguay'
EXEC sp_InsCountryCode @ccISO2 = 'UZ',@ccISO3 = 'UZB',@ccNumCode = '860',@ccCallingCode = '998',@ccShortName = 'Uzbekistan',@ccLongName = 'Republic of Uzbekistan'
EXEC sp_InsCountryCode @ccISO2 = 'VU',@ccISO3 = 'VUT',@ccNumCode = '548',@ccCallingCode = '678',@ccShortName = 'Vanuatu',@ccLongName = 'Republic of Vanuatu'
EXEC sp_InsCountryCode @ccISO2 = 'VA',@ccISO3 = 'VAT',@ccNumCode = '336',@ccCallingCode = '39',@ccShortName = 'Vatican City',@ccLongName = 'State of the Vatican City'
EXEC sp_InsCountryCode @ccISO2 = 'VE',@ccISO3 = 'VEN',@ccNumCode = '862',@ccCallingCode = '58',@ccShortName = 'Venezuela',@ccLongName = 'Bolivarian Republic of Venezuela'
EXEC sp_InsCountryCode @ccISO2 = 'VN',@ccISO3 = 'VNM',@ccNumCode = '704',@ccCallingCode = '84',@ccShortName = 'Vietnam',@ccLongName = 'Socialist Republic of Vietnam'
EXEC sp_InsCountryCode @ccISO2 = 'VG',@ccISO3 = 'VGB',@ccNumCode = '92',@ccCallingCode = '1+284',@ccShortName = 'Virgin Islands, British',@ccLongName = 'British Virgin Islands'
EXEC sp_InsCountryCode @ccISO2 = 'VI',@ccISO3 = 'VIR',@ccNumCode = '850',@ccCallingCode = '1+340',@ccShortName = 'Virgin Islands, US',@ccLongName = 'Virgin Islands of the United States'
EXEC sp_InsCountryCode @ccISO2 = 'WF',@ccISO3 = 'WLF',@ccNumCode = '876',@ccCallingCode = '681',@ccShortName = 'Wallis and Futuna',@ccLongName = 'Wallis and Futuna'
EXEC sp_InsCountryCode @ccISO2 = 'EH',@ccISO3 = 'ESH',@ccNumCode = '732',@ccCallingCode = '212',@ccShortName = 'Western Sahara',@ccLongName = 'Western Sahara'
EXEC sp_InsCountryCode @ccISO2 = 'YE',@ccISO3 = 'YEM',@ccNumCode = '887',@ccCallingCode = '967',@ccShortName = 'Yemen',@ccLongName = 'Republic of Yemen'
EXEC sp_InsCountryCode @ccISO2 = 'ZM',@ccISO3 = 'ZMB',@ccNumCode = '894',@ccCallingCode = '260',@ccShortName = 'Zambia',@ccLongName = 'Republic of Zambia'
EXEC sp_InsCountryCode @ccISO2 = 'ZW',@ccISO3 = 'ZWE',@ccNumCode = '716',@ccCallingCode = '263',@ccShortName = 'Zimbabwe',@ccLongName = 'Republic of Zimbabwe'


--Insert data into client
--EXEC sp_InsClient @clientFirstName = 'John', @clientLastName = 'Doe', @clientEmail = 'john@fakemail.com', @clientPhoneNumber = '555-852-1346', @clientPhoneType = 'Cell', @clientPassportNum = '534926011', @clientAddress = '532 S. Kangaroo Lane', @clientCity = 'Sydney', @ccISO2 = 'AU'

--Insert additional numbers
--EXEC sp_InsClientPhoneNumber 1, '555-963-8855', 'Home'

--Insert email preference
INSERT INTO dbo.EMAILPREFERENCE VALUES (1,1,1,0,1,0,'','','','','','','')
INSERT INTO dbo.EMAILPREFERENCE VALUES (5,1,1,0,1,1, '', '', '','','','','')
INSERT INTO dbo.EMAILPREFERENCE VALUES (6,1,1,0,1,1, '', '', '','','','','')

--Insert email data
--EXEC sp_InsEmailSent 'Jan 1 2012 1:29PM', 'I want an apartment that is close to the city in January 2013', 1, "Hello"
	
--Insert properties
--EXEC sp_InsProperty '712', 'Brand new 10TH floor studio with the best views of the Ping River. This is 100 Ft from the Holiday Inn where I have 5 apartments.', 'http://www.chiangmaiapartmentrental.com/p712.html', '123 Unknown St.'

--Insert task types    
EXEC sp_InsTaskType @taskTypeName = 'System Generated'
EXEC sp_InsTaskType @taskTypeName = 'Pickup'
EXEC sp_InsTaskType @taskTypeName = 'Dropoff'
EXEC sp_InsTaskType @taskTypeName = 'Groceries'
EXEC sp_InsTaskType @taskTypeName = 'Other'	
					

	
--Insert reservations  (@resStartDate, @resEndDate, @resQuotedPrice, @propID, @clientID, @userID
--EXEC sp_InsReservation 'Nov 15 2012 12:00AM', 'Nov 22 2012 12:00AM', '525.00', 1, 1, 1 


--Insert Payments
--EXEC sp_InsPayment '262.5', 1, 1, 'Credit Card - PayPal'

--Insert tasks and task details

--DECLARE @taskIDTemp INT --Used to store the taskID for details
--EXEC sp_InsTask 1, 'Fresh Flowers', 'Other', 'Nov 22 2012 12:00AM', 0, 'Get fresh Flowers for the room', @taskID = @taskIDTemp OUTPUT	
